﻿IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Request]') AND Type in (N'U'))
DROP TABLE [dbo].[Request]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Assignee]') AND Type in (N'U'))
DROP TABLE [dbo].[Assignee]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Category]') AND Type in (N'U'))
DROP TABLE [dbo].[Category] 
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Priority]') AND Type in (N'U'))
DROP TABLE [dbo].[Priority] 
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Status]') AND Type in (N'U'))
DROP TABLE [dbo].[Status]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Disposition]') AND Type in (N'U'))
DROP TABLE [dbo].[Disposition]
GO


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Assignee]') AND Type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Assignee] (
    [Assignee ID] NVARCHAR (5)  NOT NULL,
    [Full Name] NVARCHAR (60) NULL,
    [First Name] NVARCHAR (30) NULL,
    [Last Name] NVARCHAR (30) NULL,
    [Job Title] NVARCHAR (30) NULL,
    [Email Address] NVARCHAR (80) NULL,
    [Phone Number] NVARCHAR (24) NULL,
    CONSTRAINT [Assignee_PK] PRIMARY KEY CLUSTERED ([Assignee ID] ASC)
);
INSERT INTO [dbo].[Assignee] ([Assignee ID],[Full Name],[First Name],[Last Name],[Job Title],[Email Address],[Phone Number])
VALUES ('00001','Chuck Yellig','Chuck','Yellig','Systems Analyst','BaseSRS@Live.com','513-767-7168');
INSERT INTO [dbo].[Assignee] ([Assignee ID],[Full Name],[First Name],[Last Name],[Job Title],[Email Address],[Phone Number])
VALUES ('00002','Todd Robbins','Todd','Robbins','Development Manager','BaseSRS@Live.com','704-725-7001');
INSERT INTO [dbo].[Assignee] ([Assignee ID],[Full Name],[First Name],[Last Name],[Job Title],[Email Address],[Phone Number])
VALUES ('00003','Frankie DeJesus','Frankie','DeJesus','Application Architect','BaseSRS@Live.com','704-715-7002');
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Category]') AND Type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Category] (
    [Category ID] NVARCHAR (5)  NOT NULL,
    [Category Name] NVARCHAR (25) NULL,
    [Category Description] NVARCHAR (120) NULL,
    CONSTRAINT [Category_PK] PRIMARY KEY CLUSTERED ([Category ID] ASC)
);
INSERT INTO [dbo].[Category] ([Category ID],[Category Name],[Category Description])
VALUES ('00001','Service Request','');
INSERT INTO [dbo].[Category] ([Category ID],[Category Name],[Category Description])
VALUES ('00002','Application Enhancement','');
INSERT INTO [dbo].[Category] ([Category ID],[Category Name],[Category Description])
VALUES ('00003','New Idea','');
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Priority]') AND Type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Priority] (
    [Priority ID] NVARCHAR (5)  NOT NULL,
    [Priority Name] NVARCHAR (25) NULL,
    [Priority Description] NVARCHAR (120) NULL,
    CONSTRAINT [Priority_PK] PRIMARY KEY CLUSTERED ([Priority ID] ASC)
);
INSERT INTO [dbo].[Priority] ([Priority ID],[Priority Name],[Priority Description])
VALUES ('00001','Low','');
INSERT INTO [dbo].[Priority] ([Priority ID],[Priority Name],[Priority Description])
VALUES ('00002','Medium','');
INSERT INTO [dbo].[Priority] ([Priority ID],[Priority Name],[Priority Description])
VALUES ('00003','High','');
INSERT INTO [dbo].[Priority] ([Priority ID],[Priority Name],[Priority Description])
VALUES ('00004','Critical','');
END
GO


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Status]') AND Type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Status] (
    [WorkStatus ID] NVARCHAR (5)  NOT NULL,
    [WorkStatus Name] NVARCHAR (25) NULL,
    [WorkStatus Description] NVARCHAR (120) NULL,
    CONSTRAINT [WorkStatus_PK] PRIMARY KEY CLUSTERED ([WorkStatus ID] ASC)
);
INSERT INTO [dbo].[Status] ([WorkStatus ID],[WorkStatus Name],[WorkStatus Description])
VALUES ('00001','Pending','');
INSERT INTO [dbo].[Status] ([WorkStatus ID],[WorkStatus Name],[WorkStatus Description])
VALUES ('00002','Assigned','');
INSERT INTO [dbo].[Status] ([WorkStatus ID],[WorkStatus Name],[WorkStatus Description])
VALUES ('00003','In-progress','');
INSERT INTO [dbo].[Status] ([WorkStatus ID],[WorkStatus Name],[WorkStatus Description])
VALUES ('00004','Waiting','');
INSERT INTO [dbo].[Status] ([WorkStatus ID],[WorkStatus Name],[WorkStatus Description])
VALUES ('00005','Complete','');
END
GO


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Disposition]') AND Type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Disposition] (
    [Disposition ID] NVARCHAR (5)  NOT NULL,
    [Disposition Name] NVARCHAR (25) NULL,
    [Disposition Description] NVARCHAR (120) NULL,
    CONSTRAINT [Disposition_PK] PRIMARY KEY CLUSTERED ([Disposition ID] ASC)
);
INSERT INTO [dbo].[Disposition] ([Disposition ID],[Disposition Name],[Disposition Description])
VALUES ('00001','New','');
INSERT INTO [dbo].[Disposition] ([Disposition ID],[Disposition Name],[Disposition Description])
VALUES ('00002','Approved','');
INSERT INTO [dbo].[Disposition] ([Disposition ID],[Disposition Name],[Disposition Description])
VALUES ('00003','Waiting','');
INSERT INTO [dbo].[Disposition] ([Disposition ID],[Disposition Name],[Disposition Description])
VALUES ('00004','Closed','');
INSERT INTO [dbo].[Disposition] ([Disposition ID],[Disposition Name],[Disposition Description])
VALUES ('00005','Cancelled','');
END
GO


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Request]') AND Type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Request] (
    [Request ID] INT PRIMARY KEY IDENTITY,
    [Submitted] DATETIME NOT NULL DEFAULT (getdate()),
	[Description] NVARCHAR(120) NOT NULL,
    [Assignee ID] NVARCHAR (5) NULL,
    [Category ID] NVARCHAR (5) NULL,
    [Priority ID] NVARCHAR (5) NULL,
    [WorkStatus ID] NVARCHAR (5) NULL,
    [Disposition ID] NVARCHAR (5) NULL,
	[Details] NTEXT NOT NULL,
	[Attachments] VARBINARY(MAX) NULL,
   
    CONSTRAINT [Request_FK00] FOREIGN KEY ([Assignee ID]) REFERENCES [dbo].[Assignee] ([Assignee ID]) ON UPDATE CASCADE,
    CONSTRAINT [Request_FK01] FOREIGN KEY ([Category ID]) REFERENCES [dbo].[Category] ([Category ID]) ON UPDATE CASCADE,
	CONSTRAINT [Request_FK02] FOREIGN KEY ([Priority ID]) REFERENCES [dbo].[Priority] ([Priority ID]) ON UPDATE CASCADE,
	CONSTRAINT [Request_FK03] FOREIGN KEY ([WorkStatus ID]) REFERENCES [dbo].[Status] ([WorkStatus ID]) ON UPDATE CASCADE,
    CONSTRAINT [Request_FK04] FOREIGN KEY ([Disposition ID]) REFERENCES [dbo].[Disposition] ([Disposition ID]) ON UPDATE CASCADE,
);
CREATE UNIQUE NONCLUSTERED INDEX [aaaaa_0]
ON [dbo].[Request]([Request ID] ASC);
CREATE NONCLUSTERED INDEX [Assignee ID]
ON [dbo].[Request]([Assignee ID] ASC);
CREATE NONCLUSTERED INDEX [RequestType ID]
ON [dbo].[Request]([Category ID] ASC);
CREATE NONCLUSTERED INDEX [Priority ID]
ON [dbo].[Request]([Priority ID] ASC);
CREATE NONCLUSTERED INDEX [WorkStatus ID]
ON [dbo].[Request]([WorkStatus ID] ASC);
CREATE NONCLUSTERED INDEX [Disposition ID]
ON [dbo].[Request]([Disposition ID] ASC);

INSERT INTO [dbo].[Request] ([Description],[Details],[Assignee ID],[Category ID],[Priority ID],[WorkStatus ID],[Disposition ID])
VALUES ('Add a multi-select dropdown to Request to allow for selection of Pre-Requisites','...','00001','00002','00001','00001','00001');
INSERT INTO [dbo].[Request] ([Description],[Details],[Assignee ID],[Category ID],[Priority ID],[WorkStatus ID],[Disposition ID])
VALUES ('Update data model to exclude Request Details on the List and ListDetails pages','...','00001','00002','00001','00001','00001');
INSERT INTO [dbo].[Request] ([Description],[Details],[Assignee ID],[Category ID],[Priority ID],[WorkStatus ID],[Disposition ID])
VALUES ('Change Sort Order in Navigator to Descending Order','...','00001','00002','00001','00001','00001');
INSERT INTO [dbo].[Request] ([Description],[Details],[Assignee ID],[Category ID],[Priority ID],[WorkStatus ID],[Disposition ID])
VALUES ('Change Sort Order on List and ListDetails to Descending','...','00001','00002','00001','00001','00001');
INSERT INTO [dbo].[Request] ([Description],[Details],[Assignee ID],[Category ID],[Priority ID],[WorkStatus ID],[Disposition ID])
VALUES ('Change Request_Details to Details','...','00001','00002','00001','00005','00004'); /*COMPLETE*/
INSERT INTO [dbo].[Request] ([Description],[Details],[Assignee ID],[Category ID],[Priority ID],[WorkStatus ID],[Disposition ID])
VALUES ('Center Position NAV Links in Gridview','...','00001','00002','00001','00001','00001');
INSERT INTO [dbo].[Request] ([Description],[Details],[Assignee ID],[Category ID],[Priority ID],[WorkStatus ID],[Disposition ID])
VALUES ('Float Filter Dropdowns Relative to Page Sizing','Find a way to float the little fileter dropdows on the Request Tracking and Request Management Pages.','00001','00002','00001','00001','00001');
INSERT INTO [dbo].[Request] ([Description],[Details],[Assignee ID],[Category ID],[Priority ID],[WorkStatus ID],[Disposition ID])
VALUES ('Fix Problem with Work Types Table on AppTables Page','...','00001','00002','00001','00005','00004'); /*COMPLETE*/
INSERT INTO [dbo].[Request] ([Description],[Details],[Assignee ID],[Category ID],[Priority ID],[WorkStatus ID],[Disposition ID])
VALUES ('Change Details Page Heading','Change page heading to replect the request number in the title of the page.','00001','00002','00001','00005','00004'); /*COMPLETE*/
INSERT INTO [dbo].[Request] ([Description],[Details],[Assignee ID],[Category ID],[Priority ID],[WorkStatus ID],[Disposition ID])
VALUES ('Add Paging to the Bottom of Details.aspx','...','00001','00002','00001','00001','00001');
INSERT INTO [dbo].[Request] ([Description],[Details],[Assignee ID],[Category ID],[Priority ID],[WorkStatus ID],[Disposition ID])
VALUES ('Remove Delete from Details.aspx','...','00001','00002','00001','00005','00004'); /*COMPLETE*/
INSERT INTO [dbo].[Request] ([Description],[Details],[Assignee ID],[Category ID],[Priority ID],[WorkStatus ID],[Disposition ID])
VALUES ('Add Request ID to ListDetails and Details pages','...','00001','00002','00001','00005','00004'); /*COMPLETE*/
INSERT INTO [dbo].[Request] ([Description],[Details],[Assignee ID],[Category ID],[Priority ID],[WorkStatus ID],[Disposition ID])
VALUES ('Create Friendly Names for Column Headings and Labels','...','00001','00002','00001','00001','00001');
INSERT INTO [dbo].[Request] ([Description],[Details],[Assignee ID],[Category ID],[Priority ID],[WorkStatus ID],[Disposition ID])
VALUES ('Format Description and Details on the Insert Requests Page','...','00001','00002','00001','00001','00001');
INSERT INTO [dbo].[Request] ([Description],[Details],[Assignee ID],[Category ID],[Priority ID],[WorkStatus ID],[Disposition ID])
VALUES ('Add File Upload Controls to Insert Requests Page','...','00001','00002','00001','00001','00001');
INSERT INTO [dbo].[Request] ([Description],[Details],[Assignee ID],[Category ID],[Priority ID],[WorkStatus ID],[Disposition ID])
VALUES ('Remove Duplicate Submitted Column from List Requests Page','...','00001','00002','00001','00005','00004'); /*COMPLETE*/
INSERT INTO [dbo].[Request] ([Description],[Details],[Assignee ID],[Category ID],[Priority ID],[WorkStatus ID],[Disposition ID])
VALUES ('Run PRODCNT*, Run SORTALL, Run NDMCNT','...','00001','00002','00001','00005','00004'); /*COMPLETE*/
INSERT INTO [dbo].[Request] ([Description],[Details],[Assignee ID],[Category ID],[Priority ID],[WorkStatus ID],[Disposition ID])
VALUES ('Run TESTCNT*, Run SORTALL, Run CNTCMP','...','00001','00002','00001','00005','00004'); /*COMPLETE*/

END
GO
